/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package slt.Dao;

import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import connection.PostgreSQLConnection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import org.json.JSONObject;
import slt.Model.Message;
import slt.Model.User;


public class MessageDAO implements GenericDAO<Message> {
    private final PostgreSQLConnection conn = new PostgreSQLConnection();

    @Override
    public List<Message> listAll( ) {
        Message message = null;
        List<Message> messages = new LinkedList<Message>();
        
        try {
            conn.startConnection(); 
            PreparedStatement psmnt = conn.getConnection().prepareCall("SELECT messageid, username, message, date"
                    + " FROM message");
            ResultSet rs = psmnt.executeQuery();
            
            while(rs.next()) {
                message = new Message();
                
                message.setMessageId(rs.getLong("messageid"));                
                message.setMessage(rs.getString("message"));
                message.setDate(rs.getDate("date"));
                message.setUsername(rs.getString("username"));
                messages.add(message);
            } 
            
        } catch(SQLException e) {
            System.err.println("SQL Exception: " + e);
        } finally { 
            conn.closeConnection();
        }
        
        return messages;
    }
    
    public String listAllJson(){
        List<Message> messages = listAll();
        GsonBuilder builder = new GsonBuilder();
        builder.registerTypeAdapter(Message.class, new MessageSerialiser());
        Gson gson = builder.create();
        String json = gson.toJson(messages);
        return json;
    }

    @Override
    public Message getById(long id) {
        Message message = null;
        
        try {
            conn.startConnection();
            PreparedStatement psmnt = conn.getConnection().prepareStatement("SELECT messageid ,username, message, date "
                    + "FROM message WHERE username = ?");
            psmnt.setLong(1, id);
            ResultSet rs = psmnt.executeQuery();
            
            if(rs.next()) {
                message = new Message();
                
                message.setMessageId(rs.getLong("messageid"));                
                message.setMessage(rs.getString("message"));
                message.setDate(rs.getDate("date"));
                message.setUsername(rs.getString("username"));
                
                if(psmnt != null) psmnt.close();
                if(rs != null) rs.close();
            }
        } catch(SQLException e) {
            System.out.println("SQL Exception: " + e);
        } finally {
            conn.closeConnection();
        }
        return message;
    }
   

    @Override
    public int insert(Message t) {
        int success = -1;
        try {
            conn.startConnection();
            String query = "INSERT INTO message (message, date)"
                    + " VALUES (?,now())";
            PreparedStatement pstmt = conn.getConnection().prepareStatement(query);
            pstmt.setString(1, t.getMessage());
 
            pstmt.executeUpdate();
            conn.closeConnection();
        } catch (SQLException e) {
            System.out.println("SQL TopicDAO insert: " + e);
        } finally {
            conn.closeConnection();
            return success;
        }
    }

    @Override
    public int update(Message t) {
        try {
            conn.startConnection();


            String query = "UPDATE message SET  message = NULL"
                   
                    + "WHERE messageid = 11 ";

            PreparedStatement pstmt = conn.getConnection().prepareStatement(query);
            pstmt.setString(1, t.getMessage());
            pstmt.setLong(2, t.getMessageId());
            
            conn.performUpdate(pstmt);


            conn.closeConnection();
        } catch (SQLException ex) {
        }


        return -1;
    }
    
 @Override
    public int delete(Long id) {
     
       int success = -1;
        try {
            conn.startConnection();
            String query = "DELETE FROM message Where messageid = "+ id+ ";";

            PreparedStatement pstmt = conn.getConnection().prepareStatement(query);
            
            
 
            pstmt.executeUpdate();
            conn.closeConnection();
        } catch (SQLException e) {
            System.out.println("SQL TopicDAO delete: " + e);
        } finally {
            conn.closeConnection();
            return success;
        }
 }
     
    public List Show(){
        
     
     
      
       String q = "";
      
       List<User> userList = new ArrayList();
        
      
  
    
        
        try {
            conn.startConnection(); 
            String query = "SELECT userid, username, password From users"
                   
                    ;
                       System.out.println(""+query);

            PreparedStatement psmnt = conn.getConnection().prepareCall(query);
            ResultSet rs = psmnt.executeQuery();
      
            
            while(rs.next()) {
              User users = new User();
               
              users.setUserId(rs.getLong("userid"));
               users.setPassword(rs.getString("password"));              
              users.setUsername(rs.getString("username"));
               userList.add(users);
            } 
            
        } catch(SQLException e) {
            System.err.println("SQL Exception: " + e);
        } finally { 
            conn.closeConnection();
        }
        
        return userList;
    }
    
}




